package com.mydomain.debtbook;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteQueryBuilder;
import android.util.Log;

public class DebtAdapter {
	public static final String DATABASE_NAME = "debtBook.db";
	public static final String DB_TB_DEBT = "debtItems";
	public static final String DB_TB_CATEGORY = "categoryItems";
	public static final String DB_TB_GROUP = "groupItems";
	public static final String DB_TB_RESOLVE = "resolveItems";

	public static final String DB_TB_NAMES = "names";;

	private static final int DATABASE_VERSION = 4;

	public static final String _ID = "_id";
	public static final String _LENDER = "lender";
	public static final String _BORROWER = "borrower";
	public static final String _DEBT = "debt";
	public static final String _CATEGORY = "category";
	public static final String _DESCRIPTION = "description";
	public static final String _FLAG = "flag";
	public static final String _CREATE_DATE = "create_date";
	public static final String _RESOLVE_DATE = "resolve_date";

	public static final String _ITEM = "item";
	public static final String _NAME = "name";
	public static final String _GROUP = "group_name";

	public static final String _DEBTAMOUNT = "debt_amount";

	public static final int TOTAL_DEBT = 0;
	public static final int BORROWER_DEBT = 1;
	public static final int LENDER_DEBT = 2;

	private static final String[] FROM_DEBT = { _ID, _LENDER, _BORROWER, _DEBT,
			_CATEGORY, _DESCRIPTION, _CREATE_DATE, _RESOLVE_DATE };

	private static final String[] FROM_CATEGORY = { _ID, _ITEM };

	private static final String[] FROM_NAMES = { _ITEM };

	private SQLiteDatabase db;
	private final Context context;
	private DebtDBOpenHelper dbHelper;
	private SQLiteQueryBuilder qb;

	public DebtAdapter(Context _context) {
		this.context = _context;
		dbHelper = new DebtDBOpenHelper(context, DATABASE_NAME, null,
				DATABASE_VERSION);
	}

	public void close() {
		db.close();
	}

	public void open() throws SQLiteException {
		try {
			db = dbHelper.getWritableDatabase();
		} catch (SQLiteException ex) {
			db = dbHelper.getReadableDatabase();
		}

		qb = new SQLiteQueryBuilder();
	}

	private static class DebtDBOpenHelper extends SQLiteOpenHelper {
		public DebtDBOpenHelper(Context context, String name,
				CursorFactory factory, int version) {
			super(context, name, factory, version);
		}

		private static final String DATABASE_CREATE_DEBT = "create table "
				+ DB_TB_DEBT + " (" + _ID
				+ " integer primary key autoincrement, " + _LENDER
				+ " text not null, " + _BORROWER + " text not null, " + _DEBT
				+ " float, " + _CATEGORY + " text, " + _DESCRIPTION + " text, "
				+ _FLAG + " integer, " + _CREATE_DATE + " long, "
				+ _RESOLVE_DATE + " long);";

		private static final String DATABASE_CREATE_CATEGORY = "create table "
				+ DB_TB_CATEGORY + " (" + _ID
				+ " integer primary key autoincrement, " + _ITEM
				+ " text not null);";

		private static final String DATABASE_CREATE_GROUP = "create table "
				+ DB_TB_GROUP + " (" + _ID
				+ " integer primary key autoincrement, " + _GROUP
				+ " text not null, " + _NAME + " text not null);";

		private static final String DATABASE_CREATE_RESOLVE = "create table "
				+ DB_TB_RESOLVE + " (" + _ID
				+ " integer primary key autoincrement, " + _NAME
				+ " text not null);";

		@Override
		public void onCreate(SQLiteDatabase _db) {

			_db.execSQL(DATABASE_CREATE_DEBT);
			_db.execSQL(DATABASE_CREATE_CATEGORY);
			_db.execSQL(DATABASE_CREATE_GROUP);
			_db.execSQL(DATABASE_CREATE_RESOLVE);

			
			
			_db.execSQL("INSERT INTO categoryItems (item) VALUES ('Food')");
			_db.execSQL("INSERT INTO categoryItems (item) VALUES ('Rent')");
			_db.execSQL("INSERT INTO categoryItems (item) VALUES ('Utilities')");
			_db.execSQL("INSERT INTO categoryItems (item) VALUES ('Gas')");
			_db.execSQL("INSERT INTO categoryItems (item) VALUES ('Misc')");

		}

		@Override
		public void onUpgrade(SQLiteDatabase _db, int _oldVersion,
				int _newVersion) {
			Log.w("TaskDBAdapter", "Upgrading from version " + _oldVersion
					+ " to " + _newVersion
					+ ", which will destroy all old data");

			// Drop the old table.
			_db.execSQL("DROP TABLE IF EXISTS " + DB_TB_DEBT);
			_db.execSQL("DROP TABLE IF EXISTS " + DB_TB_CATEGORY);
			_db.execSQL("DROP TABLE IF EXISTS " + DB_TB_GROUP);
			_db.execSQL("DROP TABLE IF EXISTS " + DB_TB_RESOLVE);

			// Create a new one.
			onCreate(_db);
		}

	}

	// Group
	// Cursors*************************************************************************************************************************************************

	public long insertGroup(ContentValues _group) {

		return db.insertOrThrow(DB_TB_GROUP, null, _group);

	}

	public boolean groupExists(String _name) {

		return db.query(DB_TB_GROUP, new String[] { _ID }, _GROUP + "=?",
				new String[] { _name }, null, null, null).getCount() > 0;
	}

	public boolean nameInGroupExists(String _group, String _name) {

		return db.query(DB_TB_GROUP, new String[] { _ID },
				_GROUP + "=? and " + _NAME + "=?",
				new String[] { _group, _name }, null, null, null).getCount() > 0;
	}

	public Cursor getAllGroup() {

		return db.query(true, DB_TB_GROUP, new String[] { _ID, _GROUP }, null,
				null, _GROUP, null, _GROUP, null);
	}

	public Cursor getAllPersonForGroup(String _name) {

		return db.query(true, DB_TB_GROUP, new String[] { _ID, _NAME }, _GROUP
				+ "=?", new String[] { _name }, _NAME, null, _NAME, null);
	}

	public boolean deleteGroup(String _groupName) {

		return db.delete(DB_TB_GROUP, _GROUP + "=?",
				new String[] { _groupName }) > 0;
	}

	public boolean deleteNameFromGroup(String _rowIndex) {
		return db.delete(DB_TB_GROUP, _ID + "=" + _rowIndex, null) > 0;
	}

	public Cursor getNamesInGroup(String _group) {

		return db.query(true, DB_TB_GROUP, new String[] { _NAME }, _GROUP
				+ "=?", new String[] { _group }, _NAME, null, null, null);
	}

	// Resolve
	// Cursors*************************************************************************************************************************************************

	
	public boolean insertOrDeleteResolve(String _resolveStr,ContentValues _resolve) {
		

		if(db.query(DB_TB_RESOLVE, new String[] { _ID },
				_NAME + "=?", new String[] {_resolveStr}, null, null, null)
				.getCount() == 0){
			db.insertOrThrow(DB_TB_RESOLVE, null, _resolve);
			return true;
		}
		else 
		{
			db.delete(DB_TB_RESOLVE, _NAME + "=?", new String[] { _resolveStr });
			return false;
		}
	}

	public void clearResolve() {

		db.rawQuery("delete from debtItems where lender in (select name from resolveItems) or borrower in (select name from resolveItems)", null);
		db.delete(DB_TB_RESOLVE, null, null);
	}

	public boolean existResolve(String _Name){
		return db.query(DB_TB_RESOLVE, new String[] { _ID },
				_NAME + "=?", new String[] {_Name}, null, null, null)
				.getCount() > 0;
	}

	public Cursor getGroupResolveDebtSum(){
		return db.rawQuery("SELECT resolveItems._id,name, SUM(CASE WHEN lender = name THEN debt ELSE -debt END) AS debt_amount FROM resolveItems JOIN debtItems " +
				"ON (lender = name AND borrower IN (SELECT name FROM resolveItems) or borrower = name AND lender IN (SELECT name FROM resolveItems)) GROUP BY name" ,null);
	}
	
	public Cursor getChildResolveDebtSum(String _Name){
		
		return db.rawQuery("SELECT resolveItems._id,name," +
				"SUM(CASE WHEN lender = name THEN -debt ELSE debt END) as debt_amount " +
				"FROM resolveItems JOIN debtItems " +
				"ON (lender = name AND borrower = ? or borrower = name AND lender = ?) GROUP BY name" ,new String[] { _Name,_Name});
	}
	
	
	
	// Debt
	// Cursors****************************************************************************************************************************************

	public long insertDebt(ContentValues _debt) {
		return db.insertOrThrow(DB_TB_DEBT, null, _debt);
	}

	public Cursor getDebtByIdCursor(String _Id) {
		return db.query(DB_TB_DEBT, FROM_DEBT, _ID + "=" + _Id, null, null,
				null, null);
	}

	// Total Debts

	public Cursor getAllDateCursor(String _name) {
		if (_name == null) {
			_name = "";
		}
		String[] args = { _name, _name, _name };
		return db
				.rawQuery(
						"SELECT _id, create_date, SUM(CASE WHEN lender = ? THEN debt ELSE -debt END) AS debt_amount FROM debtItems" +
						"where lender = ? or borrower = ? group by create_date",
						args);

	}

	public Cursor getSumCursor() {
		return db.rawQuery("SELECT SUM(debt) from debtItems", null);
	}

	public Cursor getDebtTotalForPersonCursor(String _name, int debtType) {

		if (_name == null) {
			_name = "";
		}

		String[] args = { _name };

		switch (debtType) {
		case TOTAL_DEBT:

			return db
					.rawQuery(
							"SELECT SUM(CASE WHEN lender = ? THEN debt ELSE -debt END) AS debt_amount from debtItems" +
							" where lender = ? or borrower = ?",
							new String[] { _name, _name, _name });

		case BORROWER_DEBT:

			return db
					.rawQuery(
							"SELECT SUM(debt) AS debt_amount from debtItems where borrower = ?",
							args);
		case LENDER_DEBT:

			return db
					.rawQuery(
							"SELECT SUM(debt) AS debt_amount from debtItems where lender = ?",
							args);
		}

		return null;

	}

	// Individual Debts

	public Cursor getDebtForDateCursor(String _person, String _date) {

		String[] args = { _person, _person, _person, _person, _date };
		return db
				.rawQuery(
						"SELECT _id, CASE WHEN lender = ? THEN debt ELSE -debt END as debt_amount, category, " +
						"CASE WHEN lender  = ? THEN borrower ELSE lender END AS name " +
						"FROM debtItems where (lender = ? or borrower = ?) and ( create_date = ?)",
						args);

	}

	public Cursor getDebtForPersonCursor(String _borrower, String _lender) {
		if (_borrower == null || _lender == null) {
			_borrower = "";
			_lender = "";
		}
		String[] args = { _borrower, _borrower, _lender, _borrower, _lender };
		return db
				.rawQuery(
						"SELECT _id, CASE WHEN lender = ? THEN debt ELSE -debt END as debt_amount, category, " +
						"create_date FROM debtItems where (lender = ? and borrower = ?) or ( borrower = ? and lender = ?)",
						args);
	}

	public int updateDebt(ContentValues _debt, String _id) {

		return db.update(DB_TB_DEBT, _debt, _ID + "=" + _id, null);
	}

	public boolean removeDebt(String _rowIndex) {
		return db.delete(DB_TB_DEBT, _ID + "=" + _rowIndex, null) > 0;
	}

	// Abstract
	// Cursors*************************************************************************************************************************************************

	public Cursor getAllItemCursor(String tableName) {
		if (tableName.equals(DB_TB_DEBT)) {
			return db
					.query(DB_TB_DEBT, FROM_DEBT, null, null, null, null, null);
		} else if (tableName.equals(DB_TB_CATEGORY)) {
			return db.query(DB_TB_CATEGORY, FROM_CATEGORY, null, null, null,
					null, null);
		} else if (tableName.equals(DB_TB_NAMES)) {
			return db
					.rawQuery(
							"SELECT _id, item from (SELECT DISTINCT _id, lender AS item FROM debtItems GROUP BY item " +
							"UNION SELECT DISTINCT _id, borrower AS item FROM debtItems GROUP BY item) group by item",
							null);

		} else {
			return null;
		}
	}

	public String[] getFromList(String tableName) {
		if (tableName.equals(DB_TB_DEBT)) {
			return FROM_DEBT;
		} else if (tableName.equals(DB_TB_CATEGORY)) {
			return FROM_CATEGORY;
		} else if (tableName.equals(DB_TB_NAMES)) {
			return FROM_NAMES;
		} else {
			return null;
		}
	}

	// Person
	// Cursors*************************************************************************************************************************************************

	public Cursor getAllPersonCursor(String _person) {

		if (_person == null) {
			_person = "";
		}
		String[] args = { _person, _person, _person, _person };

		return db
				.rawQuery(
						"SELECT _id, CASE WHEN lender  = ? THEN borrower ELSE lender END AS name, " +
						"SUM(CASE WHEN lender = ? THEN debt ELSE -debt END) AS debt_amount FROM debtItems " +
						"where lender = ? or borrower = ? group by name",
						args);
	}

	// Category
	// Cursors*************************************************************************************************************************************************

	public boolean categoryExists(String _name) {

		return db.query(DB_TB_CATEGORY, new String[] { _ID }, _CATEGORY + "=?",
				new String[] { _name }, null, null, null).getCount() > 0;

	}

	
	public long insertCategory(ContentValues _category) {

		return db.insertOrThrow(DB_TB_CATEGORY, null, _category);
	}
	
	public boolean deleteCategory(String _category) {

		return db.delete(DB_TB_CATEGORY, _CATEGORY + "=?",
				new String[] { _category }) > 0;
	}
	
	
}
